use mailserver;
GO
-- nhap du lieu bang Roles
insert Roles values('admin',1);
insert Roles values('staff',2);
insert Roles values('student',3);
GO
-- nhap du lieu bang country
insert Country values('Viet Nam',1);
insert Country values('France',2);
insert Country values('Japan',3);
GO
--nhap du lieu bang city
insert City values(1,'Ha Noi',1);
insert City values(1,'Ho Chi Minh',2);
insert City values(2,'abc',3);
insert City values(2,'klm',4);
insert City values(3,'some where',5);
GO
-- nhap du lieu account la staff
insert Accounts values(2,'anhdt@fpt.aptech.ac.vn','123456','tuan anh','doan',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(2,'quandv@fpt.aptech.ac.vn','123456','quan','dang',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(2,'khaint@fpt.aptech.ac.vn','123456','khai','nguyen',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(2,'anhnt@fpt.aptech.ac.vn','123456','anh','nguyen',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
-- nhap du lieu account la student
insert Accounts values(3,'student1@fpt.aptech.ac.vn','123456','firtname','lastname',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(3,'student2@fpt.aptech.ac.vn','123456','firtname','lastname',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
insert Accounts values(3,'student3@fpt.aptech.ac.vn','123456','firtname','lastname',1,'gia lam','13216568',1,1,1,0,'10/10/2012','10/10/2012',GETDATE(),'11.22.33.44','image.jpg',1);
GO
-- nhap du lieu bang category
insert Category values('Inbox',1,0);
insert Category values('Send',2,0);
insert Category values('Draft',3,0);
insert Category values('Trash',4,0);
GO
--------------------------------- THU TUC VOI MESSAGER ---------------------------------
-- thu tuc lay danh sach message da gui
create procedure sp_getSendMessage(@accountId int)
as
select Messager.Id,Subjects,Content,AttachFile,Messager.CreateDate,AccountTo_Id,SendMessagerStatus,SendMessager.Id as 'SendMessageId' from Messager
inner join SendMessager on Messager.Id = SendMessager.Messager_Id where AccountFrom_Id = @accountId


GO
-- thu tuc lay danh sach message da nhan
create procedure sp_getMessageInbox(@accountId int)
as
select Messager.Id,Subjects,Content,AttachFile,Messager.CreateDate,AccountFrom_Id,SendMessagerStatus,SendMessager.Id as 'SendMessageId' from Messager
inner join SendMessager on Messager.Id = SendMessager.Messager_Id where AccountTo_Id = @accountId

GO
--thu tuc them moi message
create procedure sp_createMessage(@subject nvarchar(4000),@attachfile nvarchar(1000),@content ntext,@createDate datetime,@ip nvarchar(30))
as
	insert Messager values(@subject,@attachfile,@content,@createDate,@ip);
GO
--thu tuc xoa message
create procedure sp_removeMessage(@messageId int,@sendMessageType int)
as
begin
	delete from SendMessager where Messager_Id = @messageId and SendMessagerType = @sendMessageType;
	delete from Messager where Id = @messageId and Id not in(select Messager_Id from SendMessager)
end
GO
--thu tuc lay ra messge cuoi cung
create procedure sp_getFinalMessage
as
	select top 1 * from Messager order by Id desc
GO
--thu tuc tim messge theo id
create procedure sp_findMessageById(@id int)
as
	select * from Messager where Id = @id;
GO
-- thu tuc them moi sendMessage
create procedure sp_createSendMessage(@messageId int,@categoryId int,@accountFrom int,@accountTo int,@status int)
as
	insert SendMessager values(@messageId,@categoryId,@accountFrom,@accountTo,@status);	
GO
-- thu tuc cap nhap lai trang thai cua email sau lan dau duoc doc
create procedure sp_updateSendMessageStatus(@sendMessageId int,@status int)
as
	update SendMessager set SendMessagerStatus = @status where Id = @sendMessageId;
GO
-- thu tuc tra ve so email chua duoc doc cua tai khoan nguoi nhan
-- @numberEmail nhan ve so email chua duoc doc
create procedure sp_numberEmailNotRead(@accountToId int,@numberEmail int out)
as
select @numberEmail = COUNT(SendMessagerStatus) from SendMessager 
group by SendMessagerStatus,AccountTo_Id having AccountTo_Id = @accountToId and SendMessagerStatus = 1
/*
declare @number int;
execute sp_numberEmailNotRead 2,@number out
select @number
*/
GO
------------------------------- KET THUC THU TUC VOI MESSAGER ---------------------------------
----------------------------------------------------------------------------------------------------
-------------------------------- BAT DAU THU TUC VOI ACCOUNTS ---------------------------------

/*
-- thu tuc kiem tra dang nhap account
create procedure sp_checkAccount(@email nvarchar(100),@password nvarchar(100))
as
	select * from Accounts where Email_Id = @email and Passwords = @password;
*/
GO
-- THU TUC TIM ACCOUNT THEO ID
create procedure sp_findAccountById(@id int)
as
	select * from Accounts where Id = @id;
GO
-- THU TUC TRA VE DANH SACH CUA ACCOUNTS
create procedure sp_findAllAccounts
as
	select * from Accounts;
GO
-- THU TUC XOA MOT ACCOUNT
create procedure sp_removeAccounts(@accountId int)
as
	delete from Accounts where Id = @accountId;
GO
-- THU TUC THEM MOI VA CAP NHAP ACCOUNTS
create procedure sp_addUpdateAccounts
@accountId int,
@roleId int,
@emailId nvarchar(100),
@password nvarchar(32),
@firstName nvarchar(100),
@lastName nvarchar(100),
@gender bit,
@address nvarchar(500),
@phone nvarchar(100),
@countryId int,
@cityId int,
@isActive bit,
@isChangePass bit,
@firstAccess datetime,
@lastAccess datetime,
@createDate datetime,
@ip nvarchar(50),
@avartar nvarchar(100),
@courseId int
as
begin
	if(@accountId=0)
	insert Accounts values(@roleId,@emailId,@password,@firstName,@lastName,@gender,@address,@phone,@countryId,@cityId,@isActive,@isChangePass,@firstAccess,@lastAccess,@createDate,@ip,@avartar,@courseId);
	else
	update Accounts set Email_Id = @emailId,Passwords = @password,FirstName = @firstName,LastName = @lastName,
	Gender = @gender,Addresss = @address,Phone = @phone,Country_Id = @countryId,City_Id = @cityId,
	IsActive = @isActive,IsChangePass = @isChangePass,FirstAccess = @firstAccess,LastAccess = @lastAccess,
	CreateDate = @createDate,Ip = @ip,Avartar = @avartar,Course_Id = @courseId where Id = @accountId;
end
GO
-- THỦ TỤC LỌC ACCOUNT THEO COURSE_ID
create procedure sp_findAccountByCourseId
@courseId int
as
	select * from Accounts where Course_Id = @courseId
GO
-- THỦ TỤC LỌC ACCOUNT THEO EMAIL_ID
create procedure sp_findAccountByEmail(@email nvarchar(100))
as
	select * from Accounts where Email_Id = @email;
GO
-- THỦ TỤC LỌC ACCOUNT THEO EMAIL_ID VÀ COURSE_ID
create procedure sp_findAccountByEmailAndCourse
@courseId int,
@emailId nvarchar(100)
as
	select * from Accounts where Email_Id = @emailId and Course_Id = @courseId
GO

 -------------------------------------- KET THUC THU TUC VOI ACCOUNTS -------------------------
 --------------------------------------------------------------------------------------------
 ------------------------------ BAT DAU THU TUC VOI ROLES -------------------------------
 /*
 
 /*
 * THU TUC THEM MOI VA CAP NHAP ROLES
 */
 GO
create proc sp_AddUpdateRoles
@id int,
@roleName varchar(50),
@isDisplay int
as
if (@id = 0)
	insert into Roles(RoleName,IsDisplay) values(@roleName,@isDisplay)
else
	update Roles set RoleName=@roleName, IsDisplay=@isDisplay where Id=@id
GO
-- THU TUC LAY TAT CA ROLES
create proc getAllRoles
as
select * from Roles
GO
-- THU TUC LAY ROLES THEO ID
create proc sp_getRoleByID
@id int
as
select * from Roles where Id=@id
GO
-- THU TUC XOA ROLES THEO ID
create proc sp_deleteRole
@id int
as
delete from Roles where Id=@id
GO
------------------------------ KET THU VOI ROLES ---------------------------
------------------------------ BAT DAU VOI COUNTRY --------------------------
-- THU TUC THEM MOI VA UPDATE COUNTRY
create proc sp_AddCountry
@name varchar(100)
as
insert into Country(CountryName) values(@name)
GO
-- THU TUC LAY TAT CA COUNTRY
create proc sp_getAllCountry
as
select * from Country
GO
-- THU TUC XOA COUNTRY THEO ID
create proc sp_deleteCountry
@id int
as
delete from Country where Id=@id
*/
-- SendMessageType
select * from News
GO
-- thu tuc tra ve danh sach account_to_id theo account_from_id va message_id
create procedure sp_getAccountToByAccountFrom
@accountFromId int,
@messageId int
as
select AccountTo_Id from SendMessager where AccountFrom_Id = @accountFromId and Messager_Id = @messageId
GO
select * from SendMessager
GO
-- thu tuc cap nhap category email la trash
create procedure sp_updateToTrash
@sendMessageId int
as
update SendMessager set Category_Id = 4 where Id = @sendMessageId;
------------
GO
-- thu tuc lay danh sach trash theo account id
create procedure sp_showTrash(@accountId int)
as
select Messager.Id,Subjects,Content,AttachFile,Messager.CreateDate,AccountTo_Id,AccountFrom_Id,SendMessagerStatus,SendMessager.Id as 'SendMessageId' from Messager
inner join SendMessager on Messager.Id = SendMessager.Messager_Id where Category_Id = 4 and (AccountTo_Id = @accountId or AccountFrom_Id = @accountId)
GO
execute sp_showTrash 4